package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectType;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

import util.JdbcHelper;
import java.sql.*;

public final class GraduateProjectTypeDao {
    private static GraduateProjectTypeDao graduateProjectTypeTypeDao = new GraduateProjectTypeDao();

    private GraduateProjectTypeDao() {
    }

    public static GraduateProjectTypeDao getInstance() {
        return graduateProjectTypeTypeDao;
    }

    public static Collection<GraduateProjectType> findAll() throws SQLException {
        Connection connection = JdbcHelper.getConn();
        Statement stmt = connection.createStatement();
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = stmt.executeQuery("select * from graduateprojecttype");
        Collection<GraduateProjectType> graduateProjectTypeTypes= new TreeSet<GraduateProjectType>();
        while(resultSet.next()){
            graduateProjectTypeTypes.add(new GraduateProjectType(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks")));
        }
        return graduateProjectTypeTypes;
    }

    public static GraduateProjectType find(int id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String find_sql = "select * from graduateprojecttype where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        GraduateProjectType graduateProjectType = new GraduateProjectType(id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        JdbcHelper.close(pstmt,connection);
        return graduateProjectType;
    }

    public static boolean update(GraduateProjectType graduateProjectType) throws SQLException {
        //获得连接对象
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String updateSchool_sql = "update graduateprojecttype set description=?,no=?,remarks=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(4,graduateProjectType.getId());
        pstmt.setString(1,graduateProjectType.getDescription());
        pstmt.setString(2,graduateProjectType.getNo());
        pstmt.setString(3,graduateProjectType.getRemarks());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            return false;
        }else
            return  true;
    }

    public static boolean add(GraduateProjectType graduateProjectType) throws SQLException {

        //获得连接对象
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String addGraduateProjectType_sql =
                "insert into graduateprojecttype (description,no,remarks) values " +
                        "(?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGraduateProjectType_sql);
        //为预编译参数赋值

        pstmt.setString(1,graduateProjectType.getDescription());
        pstmt.setString(2,graduateProjectType.getNo());
        pstmt.setString(3,graduateProjectType.getRemarks());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        connection.close();
        connection.close();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            return true;
        } else
            return false;
    }
    public static boolean delete(GraduateProjectType graduateProjectType) throws SQLException {
        Connection conn = JdbcHelper.getConn();
        String deleteGraduateProjectType_sql = "delete from graduateprojecttype where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteGraduateProjectType_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProjectType.getNo());
        if(pstmt.executeUpdate() == 0){
            JdbcHelper.close(pstmt,conn);
            return false;
        }else {
            JdbcHelper.close(pstmt,conn);
            return true;
        }
    }
}
